package net.suncaper.hotel.mapper.impl;

import net.suncaper.hotel.common.utils.JDBCUtils;
import net.suncaper.hotel.mapper.IReservationMapper;
import net.suncaper.hotel.model.domain.Reservation;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

/**
 * 预订数据访问实现类
 */
public class ReservationMapperImpl implements IReservationMapper {
    private QueryRunner queryRunner = new QueryRunner();

    @Override
    public Reservation findById(Integer id) {
        String sql = "SELECT * FROM reservation WHERE id = ?";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanHandler<>(Reservation.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public Reservation findByReservationNo(String reservationNo) {
        String sql = "SELECT * FROM reservation WHERE reservation_no = ?";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanHandler<>(Reservation.class), reservationNo);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Reservation> findAll() {
        String sql = "SELECT * FROM reservation ORDER BY create_time DESC";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanListHandler<>(Reservation.class));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Reservation> findByStatus(String status) {
        String sql = "SELECT * FROM reservation WHERE status = ? ORDER BY create_time DESC";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanListHandler<>(Reservation.class), status);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Reservation> findByCustomerId(Integer customerId) {
        String sql = "SELECT * FROM reservation WHERE customer_id = ? ORDER BY create_time DESC";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanListHandler<>(Reservation.class), customerId);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Reservation> findByRoomId(Integer roomId) {
        String sql = "SELECT * FROM reservation WHERE room_id = ? ORDER BY check_in_date";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanListHandler<>(Reservation.class), roomId);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Reservation> findByDateRange(Date startDate, Date endDate) {
        String sql = "SELECT * FROM reservation WHERE check_in_date >= ? AND check_in_date <= ? " +
                     "ORDER BY check_in_date";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanListHandler<>(Reservation.class), startDate, endDate);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Reservation> checkRoomAvailability(Integer roomId, Date checkInDate, Date checkOutDate) {
        String sql = "SELECT * FROM reservation WHERE room_id = ? AND status NOT IN ('CANCELLED') " +
                     "AND ((check_in_date <= ? AND check_out_date > ?) " +
                     "OR (check_in_date < ? AND check_out_date >= ?) " +
                     "OR (check_in_date >= ? AND check_out_date <= ?))";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanListHandler<>(Reservation.class),
                roomId, checkInDate, checkInDate,
                checkOutDate, checkOutDate,
                checkInDate, checkOutDate);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Reservation> findByPage(int offset, int pageSize) {
        String sql = "SELECT * FROM reservation ORDER BY create_time DESC LIMIT ?, ?";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanListHandler<>(Reservation.class), offset, pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM reservation";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new ScalarHandler<Long>());
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int insert(Reservation reservation) {
        String sql = "INSERT INTO reservation (reservation_no, customer_id, room_id, " +
                     "check_in_date, check_out_date, adults, children, total_amount, " +
                     "deposit, status, special_requests) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            return queryRunner.update(JDBCUtils.getConnection(), sql,
                reservation.getReservationNo(), reservation.getCustomerId(),
                reservation.getRoomId(), reservation.getCheckInDate(),
                reservation.getCheckOutDate(), reservation.getAdults(),
                reservation.getChildren(), reservation.getTotalAmount(),
                reservation.getDeposit(), reservation.getStatus(),
                reservation.getSpecialRequests());
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int update(Reservation reservation) {
        String sql = "UPDATE reservation SET customer_id = ?, room_id = ?, " +
                     "check_in_date = ?, check_out_date = ?, adults = ?, children = ?, " +
                     "total_amount = ?, deposit = ?, status = ?, special_requests = ? WHERE id = ?";
        try {
            return queryRunner.update(JDBCUtils.getConnection(), sql,
                reservation.getCustomerId(), reservation.getRoomId(),
                reservation.getCheckInDate(), reservation.getCheckOutDate(),
                reservation.getAdults(), reservation.getChildren(),
                reservation.getTotalAmount(), reservation.getDeposit(),
                reservation.getStatus(), reservation.getSpecialRequests(),
                reservation.getId());
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int updateStatus(Integer id, String status) {
        String sql = "UPDATE reservation SET status = ? WHERE id = ?";
        try {
            return queryRunner.update(JDBCUtils.getConnection(), sql, status, id);
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int delete(Integer id) {
        String sql = "DELETE FROM reservation WHERE id = ?";
        try {
            return queryRunner.update(JDBCUtils.getConnection(), sql, id);
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }
}